﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cwtrock.model;
using System.Data;
using System.Data.SqlClient;
using cwtrock.util;

namespace cwtrock.dao.impl
{
    class VoucherDaoImpl : BasicDaoImpl
    {
        private GetConn getConn = new GetConn();

        public Vouchers save(Vouchers voucher)
        {

            try
            {
                StringBuilder strSql = new StringBuilder();
               
                strSql.Append("INSERT INTO [VOUCHERS]");
                strSql.Append("([ABID]");
                strSql.Append(",[V_SEQNO]");
                strSql.Append(",[V_CODE]");
                strSql.Append(",[V_TYPE]");
                strSql.Append(",[V_TYPE_NAME]");
                strSql.Append(",[V_ATTACHES]");
                strSql.Append(",[V_DATE]");
                strSql.Append(",[V_CHECKER]");
                strSql.Append(",[V_CHECKERID]");
                strSql.Append(",[V_MAKER]");
                strSql.Append(",[V_MAKERID]");
                strSql.Append(",[V_POSTER]");
                strSql.Append(",[V_POSTERID]");
                strSql.Append(",[V_CHECKSTAT]");
                strSql.Append(",[V_POSTSTAT]");
                strSql.Append(",[V_CLOSEDSTAT])");
                strSql.Append(" VALUES");
                strSql.Append("( @abid         ");
                strSql.Append(",@v_seqno         ");
                strSql.Append(",@v_code       ");
                strSql.Append(",@v_type       ");
                strSql.Append(",@v_type_name   ");
                strSql.Append(",@v_attaches   ");
                strSql.Append(",@v_date       ");
                strSql.Append(",@v_checker    ");
                strSql.Append(",@v_checkerid  ");
                strSql.Append(",@v_maker      ");
                strSql.Append(",@v_makerid    ");
                strSql.Append(",@v_poster      ");
                strSql.Append(",@v_posterid    ");
                strSql.Append(",@v_checkstat  ");
                strSql.Append(",@v_poststat   ");
                strSql.Append(",@v_closedstat)");
                strSql.Append(" select @@identity");

                SqlParameter[] parameters = { 
                    new SqlParameter("@abid", SqlDbType.BigInt),
                    new SqlParameter("@v_seqno", SqlDbType.Int),
                    new SqlParameter("@v_code", SqlDbType.Int),
                    new SqlParameter("@v_type", SqlDbType.VarChar,50),
                    new SqlParameter("@v_type_name", SqlDbType.VarChar,50),
                    new SqlParameter("@v_attaches", SqlDbType.BigInt),
                    new SqlParameter("@v_date",SqlDbType.DateTime),
                    new SqlParameter("@v_checker", SqlDbType.VarChar,50),
                    new SqlParameter("@v_checkerid", SqlDbType.VarChar,50),
                    new SqlParameter("@v_maker", SqlDbType.VarChar,50),     
                    new SqlParameter("@v_makerid", SqlDbType.VarChar,50), 
                    new SqlParameter("@v_poster", SqlDbType.VarChar,50),     
                    new SqlParameter("@v_posterid", SqlDbType.VarChar,50), 
                    new SqlParameter("@v_checkstat", SqlDbType.VarChar,1),
                    new SqlParameter("@v_poststat", SqlDbType.VarChar,1),  
                    new SqlParameter("@v_closedstat", SqlDbType.VarChar,1)                 
                                            
             };
                int i = 0;
                parameters[i++].Value = voucher.Abid;
                parameters[i++].Value = voucher.Vseqno;
                parameters[i++].Value = voucher.Vcode;
                parameters[i++].Value = voucher.Vtype;
                parameters[i++].Value = voucher.Vtypename;
                parameters[i++].Value = voucher.Vattaches;
                parameters[i++].Value = voucher.Vdate;
                parameters[i++].Value = voucher.Vchecker;
                parameters[i++].Value = voucher.Vcheckerid;
                parameters[i++].Value = voucher.Vmaker;
                parameters[i++].Value = voucher.Vmakerid;
                parameters[i++].Value = voucher.Vposter;
                parameters[i++].Value = voucher.Vposterid;
                parameters[i++].Value = voucher.Vcheckstat;
                parameters[i++].Value = voucher.Vpoststat;
                parameters[i++].Value = voucher.Vclosedstat;


                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);

            //    int rows = cmd.ExecuteNonQuery();
                

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    string id = reader[0].ToString();
                    voucher.Id = long.Parse(id);
                }

                cmd.Parameters.Clear();
                return voucher;
            }
            catch (Exception e)
            {
                throw e;
                return null;
            }
        }

        public List<Vouchers> getVouchersByUserId(string userid)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT [ID]");
                strSql.Append(",[ABID]");
                strSql.Append(",[V_CODE]");
                strSql.Append(",[V_TYPE_NAME]");
                strSql.Append(",[V_TYPE]");
                strSql.Append(",[V_ATTACHES]");
                strSql.Append(",[V_DATE]");
                strSql.Append(",[V_CHECKER]");
                strSql.Append(",[V_CHECKERID]");
                strSql.Append(",[V_MAKER]");
                strSql.Append(",[V_MAKERID]");
                strSql.Append(",[V_CHECKSTAT]");
                strSql.Append(",[V_POSTSTAT]");
                strSql.Append(",[V_CLOSEDSTAT]");
                strSql.Append(",[V_SEQNO]");
                strSql.Append(" FROM [VOUCHERS]");
                strSql.Append(" WHERE V_MAKERID=@userid ");
                strSql.Append(" order by id ");


                SqlParameter[] parameters = { 
                    new SqlParameter("@userid", SqlDbType.VarChar),
                                           
             };
                int i = 0;
                parameters[i++].Value = userid;

                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);

                SqlDataReader reader = cmd.ExecuteReader();
                List<Vouchers> vouls = new List<Vouchers>();

                while (reader.Read())
                {
                    Vouchers voucher = new Vouchers();
                    string vcode = reader[2].ToString();
                    string vtypename = reader[3].ToString();
                    string vtype = reader[4].ToString();
                    string vchecker = reader[7].ToString();
                    string vcheckid = reader[8].ToString();
                    string vmaker = reader[9].ToString();
                    string vmakerid = reader[10].ToString();
                    string vcheckstat = reader[11].ToString();
                    string vpoststat = reader[12].ToString();
                    string vclosedstat = reader[13].ToString();
                    string vseqno = reader[14].ToString();

                    voucher.Vcode = Int32.Parse(vcode);
                    voucher.Vtypename = vtypename;
                    voucher.Vtype = vtype;
                    voucher.Vchecker = vchecker;
                    voucher.Vcheckerid = vcheckid;
                    voucher.Vmaker = vmaker;
                    voucher.Vmakerid = vmakerid;
                    voucher.Vcheckstat = vcheckstat;
                    voucher.Vpoststat = vpoststat;
                    voucher.Vclosedstat = vclosedstat;
                    voucher.Vseqno = Int32.Parse(vseqno);

                    vouls.Add(voucher);

                }

                cmd.Parameters.Clear();
                return vouls;
            }catch(Exception e){
            
               throw e;
               return  null;
            }
        }

        public int getVouchersCntByUserAndSeqno(string seqno, string userid)
        {

            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT [ID]");
                strSql.Append(",[ABID]");
                strSql.Append(",[V_CODE]");
                strSql.Append(",[V_TYPE_NAME]");
                strSql.Append(",[V_TYPE]");
                strSql.Append(",[V_ATTACHES]");
                strSql.Append(",[V_DATE]");
                strSql.Append(",[V_CHECKER]");
                strSql.Append(",[V_CHECKERID]");
                strSql.Append(",[V_MAKER]");
                strSql.Append(",[V_MAKERID]");
                strSql.Append(",[V_CHECKSTAT]");
                strSql.Append(",[V_POSTSTAT]");
                strSql.Append(",[V_CLOSEDSTAT]");
                strSql.Append(",[V_SEQNO]");
                strSql.Append(" FROM [VOUCHERS]");
                strSql.Append(" WHERE V_MAKERID = @userid ");
                strSql.Append(" AND V_SEQNO = @seqno ");
               

                SqlParameter[] parameters = { 
                   
                     new SqlParameter("@userid", SqlDbType.VarChar),
                     new SqlParameter("@seqno", SqlDbType.VarChar),
                   
                                           
             };
                int i = 0;
                parameters[i++].Value = userid;
                parameters[i++].Value = seqno;
              

                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);

              //  int row = cmd.ExecuteNonQuery();
                int rows = 0;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    rows++;
                }
              
                cmd.Parameters.Clear();
                return rows;
            }
            catch (Exception e)
            {

                throw e;
                return 0;
            }
        }

        public int getVouchersCntByUserAndVcode(string vcode,string vtype, string userid)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT [ID]");
                strSql.Append(",[ABID]");
                strSql.Append(",[V_CODE]");
                strSql.Append(",[V_TYPE_NAME]");
                strSql.Append(",[V_TYPE]");
                strSql.Append(",[V_ATTACHES]");
                strSql.Append(",[V_DATE]");
                strSql.Append(",[V_CHECKER]");
                strSql.Append(",[V_CHECKERID]");
                strSql.Append(",[V_MAKER]");
                strSql.Append(",[V_MAKERID]");
                strSql.Append(",[V_CHECKSTAT]");
                strSql.Append(",[V_POSTSTAT]");
                strSql.Append(",[V_CLOSEDSTAT]");
                strSql.Append(",[V_SEQNO]");
                strSql.Append(" FROM [VOUCHERS]");
                strSql.Append(" WHERE V_MAKERID = @userid ");
                strSql.Append(" AND V_CODE = @vcode ");
                strSql.Append(" AND V_TYPE = @vtype ");


                SqlParameter[] parameters = { 
                    new SqlParameter("@vcode", SqlDbType.VarChar),
                    new SqlParameter("@vtype", SqlDbType.VarChar),
                    new SqlParameter("@userid", SqlDbType.VarChar),
                   
                                           
             };
                int i = 0;
                parameters[i++].Value = vcode;
                parameters[i++].Value = vtype;
                parameters[i++].Value = userid;

                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);

                int rows = 0;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    rows++;
                }

                cmd.Parameters.Clear();
                return rows;

            }
            catch (Exception e)
            {

                throw e;
                return 0;
            }
        }

        public List<Vouchers> getVouchersByCondition(Vouchers vou) {

            if (vou == null)
                return null;

            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT [ID]");
                strSql.Append(",[ABID]");
                strSql.Append(",[V_CODE]");
                strSql.Append(",[V_TYPE_NAME]");
                strSql.Append(",[V_TYPE]");
                strSql.Append(",[V_ATTACHES]");
                strSql.Append(",[V_DATE]");
                strSql.Append(",[V_CHECKER]");
                strSql.Append(",[V_CHECKERID]");
                strSql.Append(",[V_MAKER]");
                strSql.Append(",[V_MAKERID]");
                strSql.Append(",[V_CHECKSTAT]");
                strSql.Append(",[V_POSTSTAT]");
                strSql.Append(",[V_CLOSEDSTAT]");
                strSql.Append(",[V_SEQNO]");
                strSql.Append(" FROM [VOUCHERS]");
                strSql.Append(" WHERE [V_MAKERID] = @userid");

                if (!(vou.Vpoststat.Equals("0")))
                   strSql.Append(" AND [V_POSTSTAT] = @v_poststat");
                if (!(vou.Vclosedstat.Equals("0")))
                   strSql.Append(" AND [V_CLOSEDSTAT] = @v_closedstat");
                strSql.Append(" ORDER BY [V_TYPE],[V_CODE]");


                SqlParameter[] parameters = { 
                    new SqlParameter("@userid", SqlDbType.VarChar,50),
                    new SqlParameter("@v_poststat", SqlDbType.VarChar,1),
                    new SqlParameter("@v_closedstat", SqlDbType.VarChar,1),
                                           
             };
                int i = 0;
                parameters[i++].Value = vou.Vmakerid;
                parameters[i++].Value = vou.Vpoststat;
                parameters[i++].Value = vou.Vclosedstat;

                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);

                SqlDataReader reader = cmd.ExecuteReader();
                List<Vouchers> vouls = new List<Vouchers>();

                while (reader.Read())
                {
                    Vouchers voucher = new Vouchers();
                    string vid = reader[0].ToString();
                    string vcode = reader[2].ToString();
                    string vtypename = reader[3].ToString();
                    string vtype = reader[4].ToString();
                    string vdate = reader[6].ToString();                    
                    string vchecker = reader[7].ToString();
                    string vcheckid = reader[8].ToString();
                    string vmaker = reader[9].ToString();
                    string vmakerid = reader[10].ToString();
                    string vcheckstat = reader[11].ToString();
                    string vpoststat = reader[12].ToString();
                    string vclosedstat = reader[13].ToString();
                    string vseqno = reader[14].ToString();

                    voucher.Id =long.Parse(vid);
                    voucher.Vcode = Int32.Parse(vcode);
                    voucher.Vtypename = vtypename;
                    voucher.Vtype = vtype;
                    voucher.Vdatestr = vdate;
                    voucher.Vchecker = vchecker;
                    voucher.Vcheckerid = vcheckid;
                    voucher.Vmaker = vmaker;
                    voucher.Vmakerid = vmakerid;
                    voucher.Vcheckstat = vcheckstat;
                    voucher.Vpoststat = vpoststat;
                    voucher.Vclosedstat = vclosedstat;
                    voucher.Vseqno = Int32.Parse(vseqno);

                    vouls.Add(voucher);

                }

                cmd.Parameters.Clear();
                return vouls;
            }
            catch (Exception e)
            {

                throw e;
                return null;
            }
        }

        public int update(Vouchers voucher)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("UPDATE [VOUCHERS]");
                strSql.Append("SET[ABID]=@abid");
                strSql.Append(",[V_CODE]=@v_code");
                strSql.Append(",[V_TYPE_NAME]=@v_type_name");
                strSql.Append(",[V_TYPE]=@v_type");
                strSql.Append(",[V_ATTACHES]=@v_attaches");
                strSql.Append(",[V_DATE]=@v_date");
                strSql.Append(",[V_CHECKER]=@v_checker");
                strSql.Append(",[V_CHECKERID]=@v_checkerid");
                strSql.Append(",[V_MAKER]=@v_maker");
                strSql.Append(",[V_MAKERID]=@v_makerid");
                strSql.Append(",[V_POSTER]=@v_poster");
                strSql.Append(",[V_POSTERID]=@v_posterid");
                strSql.Append(",[V_CHECKSTAT]=@v_checkstat");
                strSql.Append(",[V_POSTSTAT]=@v_poststat");
                strSql.Append(",[V_CLOSEDSTAT]=@v_closedstat");
                strSql.Append(",[V_SEQNO]=@v_seqno");
                strSql.Append("  WHERE  [ID] = @id");

                SqlParameter[] parameters = { 
                    new SqlParameter("@abid", SqlDbType.BigInt),
                    new SqlParameter("@v_seqno", SqlDbType.Int),
                    new SqlParameter("@v_code", SqlDbType.Int),
                    new SqlParameter("@v_type", SqlDbType.VarChar,50),
                    new SqlParameter("@v_type_name", SqlDbType.VarChar,50),
                    new SqlParameter("@v_attaches", SqlDbType.BigInt),
                    new SqlParameter("@v_date",SqlDbType.DateTime),
                    new SqlParameter("@v_checker", SqlDbType.VarChar,50),
                    new SqlParameter("@v_checkerid", SqlDbType.VarChar,50),
                    new SqlParameter("@v_maker", SqlDbType.VarChar,50),     
                    new SqlParameter("@v_makerid", SqlDbType.VarChar,50), 
                    new SqlParameter("@v_poster", SqlDbType.VarChar,50),     
                    new SqlParameter("@v_posterid", SqlDbType.VarChar,50), 
                    new SqlParameter("@v_checkstat", SqlDbType.VarChar,1),
                    new SqlParameter("@v_poststat", SqlDbType.VarChar,1),  
                    new SqlParameter("@v_closedstat", SqlDbType.VarChar,1),
                    new SqlParameter("@id", SqlDbType.BigInt)
                                            
             };
                int i = 0;
                parameters[i++].Value = voucher.Abid;
                parameters[i++].Value = voucher.Vseqno;
                parameters[i++].Value = voucher.Vcode;
                parameters[i++].Value = voucher.Vtype;
                parameters[i++].Value = voucher.Vtypename;
                parameters[i++].Value = voucher.Vattaches;
                parameters[i++].Value = voucher.Vdate;
                parameters[i++].Value = voucher.Vchecker;
                parameters[i++].Value = voucher.Vcheckerid;
                parameters[i++].Value = voucher.Vmaker;
                parameters[i++].Value = voucher.Vmakerid;
                parameters[i++].Value = voucher.Vposter;
                parameters[i++].Value = voucher.Vposterid;
                parameters[i++].Value = voucher.Vcheckstat;
                parameters[i++].Value = voucher.Vpoststat;
                parameters[i++].Value = voucher.Vclosedstat;
                parameters[i++].Value = voucher.Id;


                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);

                int rows = cmd.ExecuteNonQuery();

                return rows;

            }
            catch (Exception ex) {
                throw ex;
                return 0;
            }
        
        }

        public int delete(Vouchers voucher)
        {
                SqlConnection sqlConnection = getConn.GetCon();
                SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();  
            try
            {                 
                StringBuilder strSql = new StringBuilder();
                strSql.Append("DELETE FROM [VOUCHERS]");
                strSql.Append("WHERE [ID] =@id");

                SqlParameter[] parameters = { 
                    new SqlParameter("@id", SqlDbType.BigInt)
                 };
       
                parameters[0].Value = voucher.Id;
                SqlCommand cmd = new SqlCommand(strSql.ToString(), sqlConnection);
                cmd.Transaction = sqlTransaction;
                cmd.Parameters.Add(parameters[0]);

                StringBuilder strSql1 = new StringBuilder();
                strSql1.Append("DELETE FROM [VRECORDS]");
                strSql1.Append("WHERE [VID] =@id");

                SqlParameter[] parameters1 = { 
                    new SqlParameter("@id", SqlDbType.BigInt)
                 };

                parameters1[0].Value = voucher.Id;
                SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), sqlConnection);
                cmd1.Transaction = sqlTransaction;
                cmd1.Parameters.Add(parameters1[0]);

                cmd.ExecuteNonQuery();
               int rows= cmd1.ExecuteNonQuery();

                sqlTransaction.Commit();

                return rows;
            }
            catch (Exception ex)
            {
                //   出错回滚  
               sqlTransaction.Rollback();   
                throw ex;
                return 0;

            }
        }

        public int postVoucher(Vouchers voucher)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("UPDATE [VOUCHERS]");
                strSql.Append("SET[V_POSTER]=@v_poster");
                strSql.Append(",[V_POSTERID]=@v_posterid");
                strSql.Append(",[V_POSTSTAT]=@v_poststat");
                strSql.Append("  WHERE  ");
                strSql.Append(" [V_POSTSTAT] = 1");

                SqlParameter[] parameters = { 
                    new SqlParameter("@v_poster", SqlDbType.VarChar,50),     
                    new SqlParameter("@v_posterid", SqlDbType.VarChar,50), 
                    new SqlParameter("@v_poststat", SqlDbType.Char,1) 
                  
                                            
             };
                int i = 0;
                parameters[i++].Value = voucher.Vposter;
                parameters[i++].Value = voucher.Vposterid;
                parameters[i++].Value = voucher.Vpoststat;
               // parameters[i++].Value = voucher.Vmakerid;


                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
              //  cmd.Parameters.Add(parameters[j++]);

                 int rows = cmd.ExecuteNonQuery();


                 StringBuilder strSql1 = new StringBuilder();
                 strSql1.Append("UPDATE [VRECORDS]");
                 strSql1.Append("SET[V_POSTER]=@v_poster");
                 strSql1.Append(",[V_POSTERID]=@v_posterid");
                 strSql1.Append(",[V_POSTSTAT]=@v_poststat");
                 strSql1.Append("  WHERE  ");
                 strSql1.Append(" [V_POSTSTAT] = 1");


                 SqlParameter[] parameters1 = { 
                    new SqlParameter("@v_poster", SqlDbType.VarChar,50),     
                    new SqlParameter("@v_posterid", SqlDbType.VarChar,50), 
                    new SqlParameter("@v_poststat", SqlDbType.Char,1) 
                  
                                            
             };
              
                 int m = 0;
                 parameters1[m++].Value = voucher.Vposter;
                 parameters1[m++].Value = voucher.Vposterid;
                 parameters1[m++].Value = voucher.Vpoststat;

                 SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), getConn.GetCon());
                 int n = 0;
                 cmd1.Parameters.Add(parameters1[n++]);
                 cmd1.Parameters.Add(parameters1[n++]);
                 cmd1.Parameters.Add(parameters1[n++]);

                 cmd1.ExecuteNonQuery();

                return rows;

            }
            catch (Exception ex)
            {
                throw ex;
                return 0;
            }

        }

        // 数要过账的凭证数
        public int countVoucherTobePosted() {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT COUNT(1)");
                strSql.Append(" FROM [VOUCHERS]");
                strSql.Append(" WHERE [V_POSTSTAT] = 1");

             

                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
            

                SqlDataReader reader = cmd.ExecuteReader();
                int count=0;
                if (reader.Read())
                {
                    count =Int32.Parse(reader[0].ToString());
                }

                return count;

            }
            catch (Exception ex)
            {
                throw ex;
                return 0;
            }

        
        }

        public List<Vouchers> getVouchersBetween(Vouchers voucher_begin, Vouchers voucher_end)
        {

            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT [ID]");
                strSql.Append(",[ABID]");
                strSql.Append(",[V_CODE]");
                strSql.Append(",[V_TYPE_NAME]");
                strSql.Append(",[V_TYPE]");
                strSql.Append(",[V_ATTACHES]");
                strSql.Append(",[V_DATE]");
                strSql.Append(",[V_CHECKER]");
                strSql.Append(",[V_CHECKERID]");
                strSql.Append(",[V_MAKER]");
                strSql.Append(",[V_MAKERID]");
                strSql.Append(",[V_CHECKSTAT]");
                strSql.Append(",[V_POSTSTAT]");
                strSql.Append(",[V_CLOSEDSTAT]");
                strSql.Append(",[V_SEQNO]");
                strSql.Append(" FROM [VOUCHERS]");
                strSql.Append(" WHERE [V_MAKERID] = @userid");

                if (voucher_begin.Vdate != null)
                    strSql.Append(" AND [V_DATE] >= @v_date_begin");
                if (voucher_end.Vdate != null)
                    strSql.Append(" AND [V_DATE] <= @v_date_end");

                strSql.Append(" ORDER BY [ID]");


                SqlParameter[] parameters = { 
                    new SqlParameter("@userid", SqlDbType.VarChar,50),
                    new SqlParameter("@v_date_begin", SqlDbType.DateTime),
                    new SqlParameter("@v_date_end", SqlDbType.DateTime),
                                           
             };
                int i = 0;
                parameters[i++].Value = voucher_begin.Vmakerid;
                parameters[i++].Value = voucher_begin.Vdate;
                parameters[i++].Value = voucher_end.Vdate;

                SqlCommand cmd = new SqlCommand(strSql.ToString(), getConn.GetCon());
                int j = 0;
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);
                cmd.Parameters.Add(parameters[j++]);

                SqlDataReader reader = cmd.ExecuteReader();
                List<Vouchers> vouls = new List<Vouchers>();

                while (reader.Read())
                {
                    Vouchers voucher = new Vouchers();
                    string vid = reader[0].ToString();
                    string vcode = reader[2].ToString();
                    string vtypename = reader[3].ToString();
                    string vtype = reader[4].ToString();
                    string vdate = reader[6].ToString();
                    string vchecker = reader[7].ToString();
                    string vcheckid = reader[8].ToString();
                    string vmaker = reader[9].ToString();
                    string vmakerid = reader[10].ToString();
                    string vcheckstat = reader[11].ToString();
                    string vpoststat = reader[12].ToString();
                    string vclosedstat = reader[13].ToString();
                    string vseqno = reader[14].ToString();

                    voucher.Id = long.Parse(vid);
                    voucher.Vcode = Int32.Parse(vcode);
                    voucher.Vtypename = vtypename;
                    voucher.Vtype = vtype;
                    voucher.Vdatestr = vdate;
                    voucher.Vchecker = vchecker;
                    voucher.Vcheckerid = vcheckid;
                    voucher.Vmaker = vmaker;
                    voucher.Vmakerid = vmakerid;
                    voucher.Vcheckstat = vcheckstat;
                    voucher.Vpoststat = vpoststat;
                    voucher.Vclosedstat = vclosedstat;
                    voucher.Vseqno = Int32.Parse(vseqno);

                    vouls.Add(voucher);

                }

                cmd.Parameters.Clear();
                return vouls;
            }
            catch (Exception e)
            {

                throw e;
                return null;
            }
        }
    }
}
